#!/usr/bin/python3
from openpyxl import load_workbook
import datetime
import os, gc
from .Base_Plugin import Base_Plugin
from utils import My_Util
from enum import Enum, auto


def get_default_value(type, val):
    """
    获取对应类型的默认值格式
    """
    if val is None:
        return val
    else:
        if "bit" in type and "b" not in val:
            return f"""b'{val}'"""
        if "decimal" in type and "." in val:
            return val.rstrip("0").rstrip(".")
        else:
            return str(val)


def get_column_name(column_name):
    """
    获取列名
    """
    if column_name in ["id", "status", "name", "type", "value", "version"]:
        return "`{}`".format(column_name)
    else:
        return column_name


def get_table_real_name(table_name, tenant):
    """
    获取表的真实名称
    """
    if tenant:
        prefix = f"""{tenant["table_prefix"]}{tenant["delimiter"]}"""
        suffix = f"""{tenant["delimiter"]}{tenant["tenant_id"]}"""
        if prefix in table_name and suffix in table_name:
            table_name = table_name.replace(prefix, "").replace(suffix, "")
    return table_name


def get_table_tenant_name(table_name, tenant):
    """
    获取带租户的表名称
    """
    table_name = "{}{}{}{}{}".format(
        tenant["table_prefix"],
        tenant["delimiter"],
        get_table_real_name(table_name.lower(), tenant),
        tenant["delimiter"],
        tenant["tenant_id"],
    )
    return table_name


class SQL_Statement_Type(Enum):
    """
    SQL指令类型
    """

    TABLE_CREATE = auto()
    TABLE_DROP = auto()
    TABLE_RENAME = auto()
    TABLE_MODIFY = auto()
    COLUMN_RENAME = auto()
    COLUMN_ADD = auto()
    COLUMN_DROP = auto()
    COLUMN_MODIFY = auto()


class SQL_Statement:
    """
    SQL指令
    """

    def __init__(self, sql_type: SQL_Statement_Type, sql: str):
        self.sql_type = sql_type
        self.sql = sql


class Table_Column:
    """
    表列
    """

    def __init__(
        self,
        table_name: str,
        table_tenant_name: str,
        column_name: str,
        ordinal_position,
        column_default,
        is_nullable,
        column_type,
        column_key,
        extra,
        column_comment,
        sql_dialect,
    ) -> None:
        self.table_name = table_name.lower()
        self.table_tenant_name = table_tenant_name.lower()
        self.modify_name_flag = "->" in column_name  # 是否修改列名
        if self.modify_name_flag:
            name_dict = My_Util.get_name_for_rename(column_name)
            self.old_column_name = name_dict["old_name"].lower()  # 旧列名
            self.column_name = name_dict["new_name"].lower()  # 新列名
        else:
            self.old_column_name = column_name.lower()
            self.column_name = self.old_column_name
        self.old_ordinal_position = ordinal_position
        self.new_ordinal_position = ordinal_position
        self.column_default = column_default
        self.is_nullable = is_nullable
        self.column_type = column_type
        self.column_key = column_key
        self.extra = extra
        self.column_comment = column_comment
        self.prev: Table_Column = None
        self.next: Table_Column = None

    def to_dict(self):
        _dict = {
            "table_name": self.table_name,
            "table_tenant_name": self.table_tenant_name,
            "column_name": self.column_name,
            "old_ordinal_position": self.old_ordinal_position,
            "new_ordinal_position": self.new_ordinal_position,
            "column_default": self.column_default,
            "is_nullable": self.is_nullable,
            "column_type": self.column_type,
            "column_key": self.column_key,
            "extra": self.extra,
            "column_comment": self.column_comment,
        }
        return _dict

    def get_sql_for_create(self):
        sql = list()
        sql.append(f"""{get_column_name(self.column_name)}""")
        sql.append(f"""{self.column_type}""")
        if self.is_nullable == "NO":
            sql.append(f"""NOT NULL""")
        else:
            sql.append(f"""NULL""")
        if self.column_default and len(self.column_default) > 0:
            sql.append(f"""DEFAULT {self.column_default}""")
        sql.append(f"""COMMENT '{self.column_comment}'""")
        if self.column_name == "id":
            sql.append("PRIMARY KEY AUTO_INCREMENT")
        return " ".join(sql)

    def get_sql_for_add(self, tenant=False) -> SQL_Statement:
        sql = list()
        sql.append(
            f"""ALTER TABLE {self.table_tenant_name if tenant else self.table_name} ADD {get_column_name(self.column_name)}"""
        )
        sql.append(f"""{self.column_type}""")
        if self.is_nullable == "NO":
            sql.append(f"""NOT NULL""")
        else:
            sql.append(f"""NULL""")
        if self.column_default and len(self.column_default) > 0:
            sql.append(f"""DEFAULT {self.column_default}""")
        sql.append(f"""COMMENT '{self.column_comment}'""")
        if self.prev:
            sql.append(f"""AFTER {get_column_name(self.prev.column_name)};""")
        statement = SQL_Statement(SQL_Statement_Type.COLUMN_ADD, " ".join(sql))
        return statement

    def get_sql_for_drop(self, tenant=False) -> SQL_Statement:
        statement = SQL_Statement(
            SQL_Statement_Type.COLUMN_DROP,
            f"""ALTER TABLE {self.table_tenant_name if tenant else self.table_name} DROP {self.column_name};""",
        )
        return statement

    def get_sql_for_rename(self, tenant=False) -> SQL_Statement:
        """
        获取列重命名的语句
        """
        statement = SQL_Statement(
            SQL_Statement_Type.COLUMN_RENAME,
            f"""ALTER TABLE {self.table_tenant_name if tenant else self.table_name} RENAME COLUMN {self.old_column_name} TO {self.column_name};""",
        )
        return statement

    def get_sql_for_modify(self, tenant=False) -> SQL_Statement:
        """
        获取修改列的语句
        """
        default_value = ""
        if self.column_default:
            default_value = "DEFAULT {}".format(self.column_default)
        sql_list = list()
        sql_list.append("ALTER TABLE")
        sql_list.append(self.table_tenant_name if tenant else self.table_name)
        sql_list.append("MODIFY COLUMN")
        col_name = get_column_name(self.column_name)
        sql_list.append(f"""{col_name}""")
        sql_list.append(f"""{self.column_type}""")
        sql_list.append(f"""{'NULL' if self.is_nullable == 'YES' else 'NOT NULL' }""")
        if len(default_value) > 0:
            sql_list.append(f"""{default_value}""")
        sql_list.append("COMMENT")
        sql_list.append(f"""'{self.column_comment}'""")
        if not self.column_name.lower() == "refcode":
            sql_list.append(f"""AFTER {get_column_name(self.prev.column_name)}""")
        statement = SQL_Statement(
            SQL_Statement_Type.COLUMN_MODIFY,
            " ".join(sql_list) + ";",
        )
        return statement


class Table:
    """
    表
    """

    def __init__(self, table_schema: str, name: str, comment: str, tenant=None) -> None:
        self.table_schema = table_schema
        self.modify_name_flag = "->" in name  # 是否修改列名
        if self.modify_name_flag:
            name_dict = My_Util.get_name_for_rename(name)
            self.old_name = get_table_real_name(
                name_dict["old_name"], tenant
            ).lower()  # 旧表名
            self.name = get_table_real_name(
                name_dict["new_name"], tenant
            ).lower()  # 新表名
        else:
            self.old_name = get_table_real_name(name.lower(), tenant).lower()
            self.name = self.old_name
        self.old_tenant_name = get_table_tenant_name(self.old_name, tenant)
        self.tenant_name = get_table_tenant_name(self.name, tenant)
        self.modify_comment_flag = "->" in comment  # 是否修改描述
        if self.modify_comment_flag:
            name_dict = My_Util.get_name_for_rename(comment)
            self.old_comment = name_dict["old_name"]
            self.comment = name_dict["new_name"]
        else:
            self.old_comment = comment
            self.comment = self.old_comment
        self.columns = list[Table_Column]()

    def to_dict(self):
        columns_dict = list()
        for column in self.columns:
            columns_dict.append(column.to_dict())
        _dict = {
            "table_schema": self.table_schema,
            "name": self.name,
            "tenant_name": self.tenant_name,
            "comment": self.comment,
            "columns": columns_dict,
        }
        return _dict

    def get_sql_for_create(self, tenant=False) -> SQL_Statement:
        column_sql = list()
        for column in self.columns:
            column_sql.append(column.get_sql_for_create())

        statement = SQL_Statement(
            SQL_Statement_Type.TABLE_CREATE,
            "CREATE TABLE {} (\r\t{}\r) {};\r\n".format(
                self.tenant_name if tenant else self.name,
                ",\n\t".join(column_sql),
                ('COMMENT "{}"'.format(self.comment)),
            ).strip(),
        )
        return statement

    def get_sql_for_drop(self, tenant=False) -> SQL_Statement:
        """
        获取删除表的SQL
        """
        statement = SQL_Statement(
            SQL_Statement_Type.TABLE_DROP,
            f"""DROP TABLE IF EXISTS {self.tenant_name if tenant else self.name};""",
        )
        return statement

    def get_sql_for_rename(self, tenant=False) -> SQL_Statement:
        """
        获取表重命名的SQL
        """
        statement = SQL_Statement(
            SQL_Statement_Type.TABLE_RENAME,
            f"""RENAME TABLE {self.old_tenant_name if tenant else self.old_name} TO {self.tenant_name if tenant else self.name};""",
        )
        return statement

    def get_sql_for_modify(self, tenant=False) -> SQL_Statement:
        """
        获取修改表属性的SQL
        """
        sql_list = list()
        sql_list.append("ALTER TABLE")
        sql_list.append(self.tenant_name if tenant else self.name)
        sql_list.append("COMMENT")
        sql_list.append("=")
        sql_list.append(f"""'{self.comment}';""")
        statement = SQL_Statement(
            SQL_Statement_Type.TABLE_MODIFY,
            " ".join(sql_list),
        )
        return statement


class Table_Diff:
    """
    表差异
    """

    def __init__(
        self,
    ) -> None:
        self.name = False
        self.comment = False

    def diff(self, table_A: Table, table_B: Table):
        """
        比对两个表的差异
        """
        _diff = False
        self.name = not table_A.name == table_B.name
        self.comment = not table_A.comment == table_B.comment
        if self.name or self.comment:
            _diff = True
        return _diff


class Table_Column_Diff:
    """
    列差异
    """

    def __init__(self) -> None:
        self.column_name = False
        self.column_default = False
        self.column_type = False
        self.is_nullable = False
        self.column_comment = False
        self.ordinal_position = False

    def diff(self, column_A: Table_Column, column_B: Table_Column):
        """
        比对两个列的差异
        """
        _diff = False
        self.column_name = not column_A.column_name == column_B.column_name
        self.column_default = not column_A.column_default == column_B.column_default
        self.column_type = not column_A.column_type == column_B.column_type
        self.is_nullable = not column_A.is_nullable == column_B.is_nullable
        self.column_comment = not column_A.column_comment == column_B.column_comment
        # self.ordinal_position = (
        #     not column_A.old_ordinal_position == column_B.old_ordinal_position
        # )
        # 当前一列或后一列不相同的时候（若存在）则表示列位置出现了偏差，需要修正
        prev_ordinal_position_diff = False
        next_ordinal_position_diff = False
        if not (column_A.column_name == "id" and column_B.column_name == "id"):
            if column_A.prev is not None and column_B.prev is not None:
                prev_ordinal_position_diff = (
                    not column_A.prev.old_column_name == column_B.prev.old_column_name
                )
            if column_A.next is not None and column_B.next is not None:
                next_ordinal_position_diff = (
                    not column_A.next.old_column_name == column_B.next.old_column_name
                )
        self.ordinal_position = prev_ordinal_position_diff or next_ordinal_position_diff
        if (
            self.column_name
            or self.column_default
            or self.column_type
            or self.is_nullable
            or self.column_comment
            or self.ordinal_position
        ):
            _diff = True
        return _diff


class DB_Diff_Table_Struct(Base_Plugin):
    """数据表差异分析插件"""

    def __init__(self, context) -> None:
        super().__init__()
        self.context = context
        self.database = self.context["db"]["database"]
        self.tenant = self.context["init_tenant"]
        self.table_prefix = self.tenant["table_prefix"]
        self.delimiter = self.tenant["delimiter"]
        self.tenant_id = self.tenant["tenant_id"]
        self.sql_dialect = self.context["sql_dialect"]

    def depend_on_db(self) -> bool:
        """
        依赖数据库
        """
        return True

    def get_tables_from_database(self, tenant=None):
        """
        从数据库中获取所有表
        """
        conn = self.get_datasource()
        with conn.cursor() as cursor:
            sql = f"""SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{self.database}' AND TABLE_NAME LIKE '{get_table_tenant_name('%', tenant)}';"""
            cursor.execute(sql)
            result = cursor.fetchall()
        tables_info = list[Table]()
        for row in result:
            table_info = Table(row[0], row[1], row[2], tenant)
            columns_info = self.get_columns_from_database(table_info.name, tenant)
            table_info.columns = columns_info
            tables_info.append(table_info)
        return tables_info

    def get_tables_from_document(self, workbook, tenant=None):
        """
        从工程文档中获取所有表
        """
        tables_info = list[Table]()
        for row in workbook["目录"]:
            if row[0].row < self.project_config["通用开始行"]:
                continue
            table_info = Table(
                self.database,
                workbook[row[2].value]["A2"].value,
                workbook[row[2].value]["A1"].value,
                tenant,
            )
            columns_info = self.get_columns_from_document(
                workbook[table_info.old_comment], self.tenant
            )
            table_info.columns = columns_info
            tables_info.append(table_info)
        return tables_info

    def get_columns_from_database(self, table_name, tenant=None):
        """
        从数据库获取表的所有的列信息
        """
        conn = self.get_datasource()
        with conn.cursor() as cursor:
            table_name = get_table_tenant_name(table_name, tenant)
            sql = f"""
    SELECT TABLE_NAME,
        COLUMN_NAME,
        ORDINAL_POSITION,
        COLUMN_DEFAULT,
        IS_NULLABLE,
        COLUMN_TYPE,
        COLUMN_KEY,
        EXTRA,
        COLUMN_COMMENT
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = '{table_name}'
    AND TABLE_SCHEMA = '{self.database}' ORDER BY ORDINAL_POSITION;
    """
            cursor.execute(sql)
            result = cursor.fetchall()
        table_columns = list[Table_Column]()
        last_column = None
        for row in result:
            table_column = Table_Column(
                get_table_real_name(row[0], tenant),
                get_table_tenant_name(row[0], tenant),
                row[1],
                row[2],
                get_default_value(row[5], row[3]),
                row[4],
                row[5],
                row[6],
                row[7],
                row[8],
                self.sql_dialect
            )
            if last_column is not None:
                table_column.prev = last_column
                last_column.next = table_column
            table_columns.append(table_column)
            last_column = table_column
        return table_columns

    def get_columns_from_document(self, sheet, tenant=None):
        """
        从工程文档获取表的所有列信息
        """
        table_columns = list[Table_Column]()
        last_column = None
        for row in sheet:
            if row[0].row < self.project_config["列字段开始行"]:
                continue
            table_column = Table_Column(
                get_table_real_name(sheet["A2"].value, tenant),
                get_table_tenant_name(sheet["A2"].value, tenant),
                row[1].value,
                row[0].row - self.project_config["列字段开始行"] + 1,
                get_default_value(row[2].value, row[5].value),
                "NO" if row[3].value == "Y" else "YES",
                row[2].value,
                "PRI" if row[1].value == "id" else "",
                "auto_increment" if row[1].value == "id" else "",
                row[6].value,
                self.sql_dialect
            )
            if last_column is not None:
                table_column.prev = last_column
                last_column.next = table_column
            table_columns.append(table_column)
            last_column = table_column
        return table_columns

    def get_diff_sql_for_tables(
        self, old_tables: list[Table], new_tables: list[Table], tenant=False
    ):
        """
        获取两张表的差异SQL
        """
        sql_statement_list = list[SQL_Statement]()
        old_names = set(map(lambda item: item.name, old_tables))
        new_names = set(map(lambda item: item.name, new_tables))
        change_old_names = set()
        change_names = set()
        sort_changed_names = list()

        table_diff = Table_Diff()

        # 若列顺序发生改变则修改顺序
        for old_table in old_tables:
            _list: list[Table] = list(
                filter(
                    lambda item: item.old_name == old_table.old_name,
                    new_tables,
                )
            )
            if len(_list) > 0:
                new_table: Table = _list.pop(0)
                for old_column in old_table.columns:
                    _column_list: list[Table_Column] = list(
                        filter(
                            lambda item: item.old_column_name
                            == old_column.old_column_name,
                            new_table.columns,
                        )
                    )
                    if len(_column_list) > 0:
                        new_column: Table_Column = _column_list.pop(0)
                        if (
                            old_column.old_ordinal_position
                            != new_column.new_ordinal_position
                        ):
                            old_column.new_ordinal_position = (
                                new_column.new_ordinal_position
                            )
                            sort_changed_names.append(old_table.old_name)
                            self.processed += 1

        # 按最新的列顺序重新排序旧表的列，避免出现列排序异常
        for sort_changed_name in sort_changed_names:
            old_table: list[Table] = list(
                filter(lambda item: item.old_name == sort_changed_name, old_tables)
            )
            if len(old_table) > 0:
                old_table: Table = old_table.pop(0)
                old_table.columns.sort(key=lambda item: item.new_ordinal_position)

        # 修改表
        for old_table in old_tables:
            _list: list[Table] = list(
                filter(
                    lambda item: item.old_name == old_table.old_name,
                    new_tables,
                )
            )
            if len(_list) > 0:
                new_table = _list.pop(0)
                if table_diff.diff(old_table, new_table):
                    if table_diff.name:
                        change_old_names.add(new_table.old_name)
                        change_names.add(new_table.name)
                        sql_statement_list.append(new_table.get_sql_for_rename(tenant))
                        self.processed += 1
                    if table_diff.comment:
                        sql_statement_list.append(new_table.get_sql_for_modify(tenant))
                        self.processed += 1
                diff_columns_sql_list = self.get_diff_sql_for_columns(
                    old_table.columns, new_table.columns, tenant
                )
                if len(diff_columns_sql_list) > 0:
                    sql_statement_list += diff_columns_sql_list
                    self.processed += 1

        create_names = new_names - old_names - change_names
        # 新增表
        for create_name in create_names:
            _list: list[Table] = list(
                filter(
                    lambda item: item.name == create_name,
                    new_tables,
                )
            )
            new_table = _list.pop(0)
            sql_statement_list.append(new_table.get_sql_for_create(tenant))
            self.processed += 1

        drop_names = old_names - new_names - change_old_names
        # 删除表
        for drop_name in drop_names:
            _list: list[Table] = list(
                filter(
                    lambda item: item.name == drop_name,
                    old_tables,
                )
            )
            old_table = _list.pop(0)
            sql_statement_list.append(old_table.get_sql_for_drop(tenant))
            self.processed += 1

        return sql_statement_list

    def get_diff_sql_for_columns(
        self,
        old_columns: list[Table_Column],
        new_columns: list[Table_Column],
        tenant=False,
    ) -> list[SQL_Statement]:
        """
        获取两张表的差异SQL
        """
        sql_statement_list = list[SQL_Statement]()
        old_names = set(map(lambda item: item.column_name, old_columns))
        new_names = set(map(lambda item: item.column_name, new_columns))
        change_old_names = set()
        change_names = set()

        table_column_diff = Table_Column_Diff()
        # 修改列
        for old_column in old_columns:  # 若已存在的列出现变化，则生成变更SQL
            _list = list(
                filter(
                    lambda item: item.old_column_name == old_column.old_column_name,
                    new_columns,
                )
            )
            if len(_list) > 0:  # 存在列，则比对列
                new_column = _list.pop(0)
                if table_column_diff.diff(old_column, new_column):
                    if table_column_diff.column_name:
                        change_old_names.add(new_column.old_column_name)
                        change_names.add(new_column.column_name)
                        sql_statement_list.append(new_column.get_sql_for_rename(tenant))
                    if (
                        table_column_diff.column_comment
                        or table_column_diff.column_default
                        or table_column_diff.column_type
                        or table_column_diff.is_nullable
                        or table_column_diff.ordinal_position
                    ):
                        sql_statement_list.append(new_column.get_sql_for_modify(tenant))

        create_names = new_names - old_names - change_names
        if len(create_names) > 0:
            # 重新排序，避免列顺序紊乱造成列依赖异常
            source_new_columns_names = list(
                map(lambda item: item.column_name, new_columns)
            )
            create_names = list(create_names)
            create_names.sort(key=lambda item: source_new_columns_names.index(item))
        # 新增列
        for create_name in create_names:
            _list: list[Table_Column] = list(
                filter(
                    lambda item: item.column_name == create_name,
                    new_columns,
                )
            )
            new_column = _list.pop(0)
            sql_statement_list.append(new_column.get_sql_for_add(tenant))

        drop_names = old_names - new_names - change_old_names
        # 删除列
        for drop_name in drop_names:
            _list: list[Table_Column] = list(
                filter(
                    lambda item: item.column_name == drop_name,
                    old_columns,
                )
            )
            old_column = _list.pop(0)
            sql_statement_list.append(old_column.get_sql_for_drop(tenant))

        return sql_statement_list

    def save_file(self, sql, dir):
        with open(dir, "w", encoding="utf-8") as f:
            f.write(sql)

    def run(self, workspace, source):
        _target_path = os.path.join(workspace, "sql", "increment")
        if not os.path.exists(_target_path):
            os.makedirs(_target_path)

        workbook = load_workbook(os.path.join(workspace, source))

        # 获取数据库中的表结构
        database_tables = self.get_tables_from_database(self.tenant)
        # 获取工程文件中的表结构
        document_tables = self.get_tables_from_document(workbook, self.tenant)
        # 对比表并获取差异SQL
        diff_tables_sql: list[SQL_Statement] = self.get_diff_sql_for_tables(
            database_tables, document_tables
        )
        diff_tables_tenant_sql: list[SQL_Statement] = self.get_diff_sql_for_tables(
            database_tables, document_tables, True
        )

        # 按类型重新排序脚本
        diff_tables_sql.sort(
            key=lambda item: SQL_Statement_Type._member_names_.index(item.sql_type.name)
        )
        diff_tables_sql = list(map(lambda item: item.sql, diff_tables_sql))
        diff_tables_tenant_sql.sort(
            key=lambda item: SQL_Statement_Type._member_names_.index(item.sql_type.name)
        )
        diff_tables_tenant_sql = list(
            map(lambda item: item.sql, diff_tables_tenant_sql)
        )

        patch_sql = "\n".join(diff_tables_sql)
        patch_tenant_sql = diff_tables_tenant_sql
        self.processed = len(patch_tenant_sql)
        patch_tenant_sql = "\n".join(patch_tenant_sql)
        patch_name = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
        self.save_file(
            patch_sql,
            os.path.join(
                _target_path,
                "{}.sql".format(patch_name),
            ),
        )
        self.save_file(
            patch_tenant_sql,
            os.path.join(
                _target_path,
                "{}-tenant.sql".format(patch_name),
            ),
        )

        del workbook
        gc.collect()
        return source
